﻿#include "sqlite_utils.h"
#include "utils.h"

#include <QFile>
#include <QSqlError>
#include <QSqlQuery>
#include <QVariant>
#include <QJsonArray>
#include <QJsonObject>
#include <QJsonDocument>
#include <QJsonParseError>
#include <QDomDocument>

#include <iostream>
using namespace std;

QString sqlite_utils::get_contains_name(char *db_file)
{
	QString contains_name = QString(db_file);
	contains_name = contains_name.replace(":", "");
	contains_name = contains_name.replace("\\", "_");
	contains_name = contains_name.replace("/", "_");
	contains_name = contains_name.replace(".", "_");
	//contains_name = "qt_sql_connection_" + contains_name;

	return contains_name;
}

int sqlite_utils::delete_db(char *db_file)
{
	QString contains_name = get_contains_name(db_file);

	QSqlDatabase database;
	if (QSqlDatabase::contains(contains_name))
	{
		database = QSqlDatabase::database(contains_name);
		database.close();
		QSqlDatabase::removeDatabase(contains_name);
	}

	QFile file(db_file);
	if (file.exists())
	{
		file.remove();
	}

	return 0;
}

QMap< QString, QMap< QString, QMap<QString, QString> > > sqlite_utils::get_sqlite_map(char *json_file, char *db_file) 
{
	sync_sqlite_map(json_file, db_file);
	return m_sqlite_map;
}

QSqlDatabase sqlite_utils::get_database(char *db_file)
{
	/*QString contains_name = get_contains_name(db_file);
	if (QSqlDatabase::contains(contains_name))
	{
		return QSqlDatabase::database(contains_name);
	}

	QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE", contains_name);*/

	QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
	database.setDatabaseName(db_file);
	if (!database.open())
	{
		cout << "Error: failed to connect database." << endl;
		database.close();
	}

	return database;
}

int sqlite_utils::sync_sqlite_map(char *json_file, char *db_file)
{
	QString contains_name = get_contains_name(db_file);
	if (!m_sqlite_map.contains(contains_name))
	{
		QFile file(json_file);
		if (file.exists())
		{
			if (file.open(QIODevice::ReadOnly))
			{
				QByteArray result = file.readAll();

				QJsonParseError jsonError;
				QJsonDocument doucment = QJsonDocument::fromJson(result, &jsonError);
				if (!doucment.isNull() && (jsonError.error == QJsonParseError::NoError))
				{
					if (doucment.isObject())
					{
						QString contains_name = get_contains_name(db_file);

						QJsonObject repData = doucment.object();
						for (QJsonObject::Iterator table_iter = repData.begin(); table_iter != repData.end(); table_iter++)
						{
							QString create_sql = "create table " + table_iter.key() + " (";

							QJsonArray array = table_iter.value().toArray();
							int nSize = array.size();

							for (int i = 0; i < nSize; i++)
							{ // 遍历数组
								QJsonObject sql_qjo_table = array.at(i).toObject();
								m_sqlite_map[contains_name][table_iter.key()][sql_qjo_table.value("name").toString()] = sql_qjo_table.value("type").toString();
							}
						}

						file.close();
						return 0;
					}
				}
				else
				{
					cout << "Error: "
						<< "fail to read " << json_file << "." << endl;
				}
			}
			else
			{
				cout << "Error: "
					<< "fail to open " << json_file << "." << endl;
			}
		}
	}
	else {
		return 0;
	}

	return -1;
}

int sqlite_utils::create_db(char *json_file, char *db_file)
{
	delete_db(db_file);

	QSqlDatabase database = get_database(db_file);
	if (database.isOpen())
	{
		if (sync_sqlite_map(json_file, db_file) == 0)
		{
			QString contains_name = get_contains_name(db_file);

			for (QMap<QString, QMap<QString, QString>>::iterator tables_iter = m_sqlite_map[contains_name].begin();
				 tables_iter != m_sqlite_map[contains_name].end(); tables_iter++)
			{
				QString create_sql = "create table " + tables_iter.key() + " (";

				for (QMap<QString, QString>::iterator keys_iter = tables_iter.value().begin();
					 keys_iter != tables_iter.value().end(); keys_iter++)
				{
					create_sql += keys_iter.key() + " " + keys_iter.value();
					create_sql += (keys_iter + 1 != tables_iter.value().end()) ? ", " : ")";
				}

				QSqlQuery sql_query(database);
				sql_query.prepare(create_sql);
				if (!sql_query.exec())
				{
					cout << "Error: fail to create table." << endl;
					database.close();
					return -1;
				}
			}
		}

		database.close();
		return 0;
	}
	else
	{
		cout << "Error: failed to connect database." << endl;
	}

	database.close();
	return -1;
}

int sqlite_utils::insert(char *json_file, char *db_file, QMap<QString, QMap<int, QMap<QString, QString>>> &insert_data_map)
{
	QSqlDatabase database = get_database(db_file);
	if (database.isOpen())
	{
		if (sync_sqlite_map(json_file, db_file) == 0)
		{
			for (QMap<QString, QMap<int, QMap<QString, QString>>>::iterator insert_data_iter = insert_data_map.begin();
				insert_data_iter != insert_data_map.end(); insert_data_iter++)
			{
				for (QMap<int, QMap<QString, QString>>::iterator data_iter = insert_data_iter.value().begin();
					data_iter != insert_data_iter.value().end(); data_iter++)
				{
					QString table_name = insert_data_iter.key();
					QString query_sql = "INSERT INTO " + table_name + " (";
					QString query_sql2;
					for (QMap<QString, QString>::iterator key_value_iter = data_iter.value().begin();
						key_value_iter != data_iter.value().end(); key_value_iter++)
					{
						query_sql += key_value_iter.key() + ((key_value_iter + 1 != data_iter.value().end()) ? ", " : ") VALUES (");
						query_sql2 += (key_value_iter + 1 != data_iter.value().end()) ? "?, " : "?)";
					}
					query_sql += query_sql2;

					QSqlQuery sql_query(database);
					sql_query.prepare(query_sql);

					QString contains_name = get_contains_name(db_file);

					for (QMap<QString, QString>::iterator key_value_iter = data_iter.value().begin();
						key_value_iter != data_iter.value().end(); key_value_iter++)
					{
						QMap<QString, QString>::iterator iter = m_sqlite_map[contains_name][table_name].find(key_value_iter.key());
						if (iter != m_sqlite_map[contains_name][table_name].end())
						{
							QString key_type = iter.value();
							if (key_type.left(3) == "int")
							{
								sql_query.addBindValue(key_value_iter.value().toInt());
							}
							else if (key_type.left(7) == "varchar")
							{
								sql_query.addBindValue(key_value_iter.value());
							}
							else if (key_type.left(7) == "INTEGER")
							{
								sql_query.addBindValue(key_value_iter.value().toLongLong());
							}
							else
							{
								cout << "Error: " << key_type.toStdString() << " is unknown type from " << QString(json_file).toStdString() << "." << endl;
								return -1;
							}
						}
					}

					if (sql_query.exec())
					{
						return 0;
					}
					else {
						cout << sql_query.lastQuery().toStdString() << endl;;
						cout << sql_query.lastError().driverText().toStdString() << endl;
					}
				}
			}
		}
	}

	cout << "Error: Fail to insert msg data." << endl;
	return -1;
}

QVector < QMap< QString, QString> > sqlite_utils::query(char *json_file, char *db_file, char *table_name, char *sql_command)
{
	QVector < QMap< QString, QString> > result_list;
	QSqlDatabase database = get_database(db_file);
	if (database.isOpen())
	{
		if (sync_sqlite_map(json_file, db_file) == 0)
		{
			QSqlQuery sql_query(database);
			sql_query.prepare("select * from " + QString(table_name) + " where " + QString(sql_command));
			if (!sql_query.exec())
			{
				cout << sql_query.lastError().driverText().toStdString() << endl;
			}
			else
			{
				int count = 0;
				while (sql_query.next())
				{
					int i = 0;
					cout << count << ":************************************************" << endl;
					QString contains_name = get_contains_name(db_file);
					QMap<QString, QString> result;
					for (QMap<QString, QString>::iterator iter = m_sqlite_map[contains_name][table_name].begin();
						iter != m_sqlite_map[contains_name][table_name].end(); iter++)
					{
						if (!sql_query.value(i).isNull()) {
							QString value = sql_query.value(i).toString();
							result.insert(iter.key(), value);
							cout << QString("%1: %2").arg(iter.key()).arg(value).toStdString() << endl;
						}
						i++;
					}
					result_list.insert(count, result);
					count++;
				}
			}
		}
	}

	return result_list;
}